package com.mrd.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;

import com.alibaba.fastjson.JSONObject;
import com.mrd.exception.ImportException;
import com.mrd.gtimp.base.entity.Company;
import com.mrd.gtimp.business.entity.Commodity;
import com.mrd.gtimp.business.entity.Declaration;
import com.mrd.gtimp.business.entity.SuiFuDan;
import com.mrd.gtimp.order.entity.DeclaContainer;

/**
 * 新的excel模板导入工具类，将excel解析为对象
 * 
 * @author han
 *
 */
public class ExcelImportUtils {
  private static final Logger logger = LogManager.getLogger(ExcelImportUtils.class);
  public static int importCount = 100;// 商品数据，集装箱导入的最大数据
  public static int fsdCount = 3;// 附税单最大导入数据
  public static String excelTabName = "平台解读表格";

  /**
   * 基本信息
   * 
   * @author han
   * @descript 数据格式： 描述|映射到对象属性:excel取值单元格
   */
  private static String str = "{\r\n" + "    \"" + excelTabName + "\": {\r\n"
      + "        \"出口口岸|export_port\": \"B2\",\r\n"
      + "        \"申报口岸|declaration_port\": \"B3\",\r\n" + "        \"报关行\": \"B4\",\r\n"
      + "        \"备案号|record_id\": \"B5\",\r\n" + "        \"合同协议号|contract_no\": \"B6\",\r\n"
      + "        \"签约地点|singing_place\": \"B7\",\r\n"
      + "        \"签约日期|contract_date\": \"B8\",\r\n" + "        \"发票号|invoice_no\": \"B9\",\r\n"
      + "        \"出口日期|export_date\": \"B10\",\r\n" + "        \"申报日期|report_date\": \"B11\",\r\n"
      + "        \"收发货人|company_name\": \"B12\",\r\n" + "        \"海关编号|company_co\": \"B13\",\r\n"
      + "        \"统一社会信用代码|company_co_unite\": \"B14\",\r\n" + "        \"公司地址\": \"B15\",\r\n"
      + "        \"公司电话\": \"B16\",\r\n" + "        \"生产销售单位|fahuo_company_name\": \"B17\",\r\n"
      + "        \"海关编号或组织机构代码|fahuo_company_co\": \"B18\",\r\n"
      + "        \"统一社会信用代码|fahuo_company_co_unite\": \"B19\",\r\n"
      + "        \"外商|buyer\": \"B20\",\r\n" + "        \"外商地址\": \"B21\",\r\n"
      + "        \"运输方式|transport_mode\": \"B22\",\r\n"
      + "        \"运输工具名称|transport_na\": \"B23\",\r\n" + "        \"航次号|voyage_no\": \"B24\",\r\n"
      + "        \"提运单号|bl_no\": \"B25\",\r\n" + "        \"监管方式|trade_mode\": \"B26\",\r\n"
      + "        \"征免性质|levymode\": \"B27\",\r\n" + "        \"运抵国|arrived_country\": \"B28\",\r\n"
      + "        \"贸易国|trading_country\": \"B29\",\r\n"
      + "        \"指运港|arrived_port\": \"B30\",\r\n" + "        \"件数|goods_count\": \"B31\",\r\n"
      + "        \"包装种类|goods_package\": \"B32\",\r\n" + "        \"成交方式|deal_mode\": \"B33\",\r\n"
      + "        \"运费(计费类型)|freight1\": \"B34\",\r\n" + "        \"运费(数量)|freight2\": \"B35\",\r\n"
      + "        \"运费(币制)|freight3\": \"B36\",\r\n" + "        \"保费（计费类型）|premium1\": \"B37\",\r\n"
      + "        \"保费（数量)|premium2\": \"B38\",\r\n" + "        \"保费(币制)|premium3\": \"B39\",\r\n"
      + "        \"杂费（计费类型)|otherfee1\": \"B40\",\r\n"
      + "        \"杂费（数量)|otherfee2\": \"B41\",\r\n" + "        \"杂费（币制）|otherfee3\": \"B42\",\r\n"
      + "        \"毛重|gross_weight\": \"B43\",\r\n" + "        \"净重|net_weight\": \"B44\",\r\n"
      + "        \"许可证号|license_no\": \"B45\",\r\n" + "        \"报关单类型|customs_type\": \"B46\",\r\n"
      + "        \"境内货源地|goods_addr\": \"B47\",\r\n"
      + "        \"其他货源地|other_goods_addr\": \"B48\",\r\n" + "        \"箱唛|carton\": \"B49\",\r\n"
      + "        \"备注|remark\": \"B50\",\r\n"
      + "        \"特殊关系确认|special_relationship_confirm\": \"H7\",\r\n"
      + "        \"价格影响确认|price_impact_confirm\": \"J7\",\r\n"
      + "        \"支付特许权使用费确认|payment_royaltils_confirm\": \"H8\",\r\n"
      + "        \"结汇方式|lc_type\": \"B51\",\r\n" + "        \"装运期\": \"B52\"\r\n" + "    }\r\n"
      + "}";

  /**
   * 商品数据
   * 
   * @author han
   * @descript 数据格式： 描述|映射到对象属性:excel取值单元格
   **/
  private static String productStr = "{" + excelTabName
      + ":{\"商品序号|index_num\": \"E2\",\"手册备案序号|record_id\":\"E3\",\"商品编号|code_t\":\"E4\",\"商品名称|name\":\"E5\","
      + "\"规格型号|key_elements\":\"E6\",\"申报要素|goods_mode\":\"E7\",\"计价数量|count\":\"E8\",\"计价单位|unit\":\"E9\","
      + "\"总价|total_price\":\"E10\",\"法定数量|legal_count\":\"E11\",\"法定单位|legal_unit\":\"E12\",\"单价|unit_price\":\"E13\","
      + "\"第二数量|second_count\":\"E14\",\"第二计量单位|second_unit\":\"E15\",\"币制|currency\":\"E16\",\"目的国|destination\":\"E17\","
      + "\"原产国|origin_country\":\"E18\",\"征免|levymode\":\"E19\",\"包装种类|package_type\":\"E20\",\"包装件数|goods_number\":\"E21\","
      + "\"毛重|goods_gross_weight\":\"E22\",\"净重|goods_net_weight\":\"E23\",\"版本号|version_no\":\"E24\",\"货号|goods_no\":\"E25\","
      + "\"工缴费|processing_fee\":\"E26\",\"装箱单备注内容|packinglist_remark\":\"E27\"}}";

  // 税附单
  private static String SuiFuDanStr =
      "{" + excelTabName + ": {\"suifudan_co\": \"G3\",\"suifudan_no\":\"H3\"}}";

  // 集装箱
  private static String declaContainerStr =
      "{" + excelTabName + ": {\"containerNo\": \"G11\",\"containerStandard\":\"H11\"}}";

  public static void main(String[] args) throws Exception {
    System.out.println("================读取excel文件===================");
    InputStream in = new FileInputStream(new File("d:\\excel_export_declaration.xls"));// 获取文件
    excelToBean(in);
  }

  /**
   * excel数据转换为Declaration对象
   * 
   * @author han
   */
  public static MsgExcel excelToBean(InputStream in) throws Exception {
    Declaration declaration = new Declaration();
    MsgExcel rtnExcelMsg = new MsgExcel();
    List<String> errorList = new ArrayList<String>();
    logger.info("================读取excel文件===================");
    HSSFWorkbook xb = new HSSFWorkbook(in);// 获取poi对象
    if (xb.getSheet(excelTabName) == null) {
      errorList.add("模板格式错误");
    } else {
      JSONObject obj = JSONObject.parseObject(str);
      JSONObject productObj = JSONObject.parseObject(productStr);
      JSONObject SuiFuDanStrObj = JSONObject.parseObject(SuiFuDanStr);
      JSONObject declaContainerObj = JSONObject.parseObject(declaContainerStr);

      HSSFSheet systemSheet = null;
      FormulaEvaluator evaluator = xb.getCreationHelper().createFormulaEvaluator();

      MsgExcel excelMsg = base(xb, systemSheet, obj, evaluator);// 处理基本填写表单字段
      declaration = (Declaration) excelMsg.get("declaration");
      if ((int) excelMsg.get("code") < 0) {
        errorList = (List<String>) excelMsg.get("message");
      }
      MsgExcel productMsg = productDeal(xb, systemSheet, productObj, evaluator);// 商品数据处理
      List<Commodity> commoditys = (List<Commodity>) productMsg.get("commoditys");
      if ((int) productMsg.get("code") < 0) {
        for (String err : (List<String>) productMsg.get("message")) {
          errorList.add(err);
        }
      }
      List<SuiFuDan> suiFuDans = SuiFuDanDeal(xb, systemSheet, SuiFuDanStrObj, evaluator);// 税单处理
      List<DeclaContainer> declaContainers =
          declaContainerDeal(xb, systemSheet, declaContainerObj, evaluator);// 集装箱数据处理
      declaration.setCommodities(commoditys);
      declaration.setSuiFuDans(suiFuDans);
      declaration.setDeclaContainers(declaContainers);
      logger.info("解析结束");
    }
    rtnExcelMsg.put("declaration", declaration);
    rtnExcelMsg.put("message", errorList);
    return rtnExcelMsg;
  }

  /**
   * 基本数据处理
   * 
   * @author han
   */
  public static MsgExcel base(HSSFWorkbook xb, HSSFSheet systemSheet, JSONObject obj,
      FormulaEvaluator evaluator) {
    Declaration declaration = null;
    MsgExcel mesExcel = new MsgExcel();
    for (Map.Entry<String, Object> entry : obj.entrySet()) {
      systemSheet = xb.getSheet(entry.getKey());
      JSONObject val = (JSONObject) entry.getValue();
      declaration = new Declaration();
      HashMap<String, Object> map = new HashMap<String, Object>();

      for (Map.Entry<String, Object> itemEntry : val.entrySet()) {
        CellReference cellReference = new CellReference((String) itemEntry.getValue());
        Row row = systemSheet.getRow(cellReference.getRow());
        Cell cell = row.getCell(cellReference.getCol());
        // logger.info(itemEntry.getKey() + ":"
        // + getRowItemVal(cellReference, row, cell, evaluator, itemEntry.getKey()));
        String excelVal = "";
        try {
          excelVal = getRowItemVal(cellReference, row, cell, evaluator, itemEntry.getKey());
        } catch (ImportException ex) {
          mesExcel.add(ex.getMessage());
        }
        String keyStr = itemEntry.getKey();
        if (keyStr.indexOf("|") > 0) {
          String[] keyAry = keyStr.split("\\|");

          // 布尔类型处理，需要二次转换
          if (StringUtils.isNotEmpty(excelVal) && (keyStr.equals("special_relationship_confirm")
              || keyStr.equals("special_relationship_confirm")
              || keyStr.equals("price_impact_confirm"))) {
            if (excelVal.equals("是")) {
              excelVal = "0";
            } else {
              excelVal = "1";
            }
            map.put(keyAry[1], excelVal);
          } else {
            map.put(keyAry[1], excelVal);
          }

        } else {
          map.put(keyStr, excelVal);
        }
      }
      try {
        BeanUtilExt.copyProperties(declaration, map);
        // 报关行处理
        if (StringUtils.isNotEmpty((String) map.get("报关行"))) {
          Company supCompany = new Company();
          supCompany.setName((String) map.get("报关行"));
          declaration.setSupCompany(supCompany);
        }
      } catch (Exception ex) {
        String errorStr = ex.getMessage();
        String fileReg = "Declaration\\.set(.*?)\\bon";
        String errorFileName = getBeanCopyErrorFileName(errorStr, fileReg);
        if (errorFileName != null) {
          errorFileName = StringUtils.uncapitalize(errorFileName);
          errorFileName = errorFileName.trim();
          for (Map.Entry<String, Object> itemEntry : val.entrySet()) {
            String nameKey = itemEntry.getKey();
            if (nameKey.indexOf(errorFileName.trim()) > 0) {
              mesExcel.add(nameKey.substring(0, nameKey.length() - errorFileName.length()));
            }
          }
        } else {
          mesExcel.add("表头数据存在非法数据类型");
        }
      }
    }
    mesExcel.put("declaration", declaration);
    return mesExcel;
  }

  /**
   * 解析柜号数据
   * 
   * @author han
   * @param xb
   * @param systemSheet
   * @param obj
   * @param evaluator
   * @return
   */
  public static List<DeclaContainer> declaContainerDeal(HSSFWorkbook xb, HSSFSheet systemSheet,
      JSONObject obj, FormulaEvaluator evaluator) {
    List<DeclaContainer> list = new ArrayList<DeclaContainer>();
    MsgExcel mesExcel = new MsgExcel();
    try {
      for (Map.Entry<String, Object> entry : obj.entrySet()) {
        systemSheet = xb.getSheet(entry.getKey());
        JSONObject val = (JSONObject) entry.getValue();

        for (int i = 0; i < importCount; i++) {
          DeclaContainer declaContainer = new DeclaContainer();
          HashMap<String, Object> valMap = new HashMap<String, Object>();
          for (Map.Entry<String, Object> itemEntry : val.entrySet()) {
            String CellReferenceId = (String) itemEntry.getValue();
            if (i != 0) {
              int cellIndex =
                  Integer.parseInt(CellReferenceId.substring(1, CellReferenceId.length())) + i;
              CellReferenceId = CellReferenceId.substring(0, 1) + cellIndex;
            }
            CellReference cellReference = new CellReference(CellReferenceId);
            Row row = systemSheet.getRow(cellReference.getRow());
            Cell cell = row.getCell(cellReference.getCol());
            // logger.info(itemEntry.getKey() + ":"
            // + getRowItemVal(cellReference, row, cell, evaluator, itemEntry.getKey()));
            String containerValue =
                getRowItemVal(cellReference, row, cell, evaluator, itemEntry.getKey());

            if (itemEntry.getKey().equals("containerStandard")) {
              if (containerValue.equals("海运20尺普通")) {
                containerValue = "S";
              } else if (containerValue.equals("海运40尺普通"))
                containerValue = "L";
            }

            valMap.put(itemEntry.getKey(), containerValue);

          }
          BeanUtils.copyProperties(declaContainer, valMap);

          if (StringUtils.isEmpty(declaContainer.getContainerNo())) {
            break;
          }
          list.add(declaContainer);
        }
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    }
    return list;
  }

  /**
   * 解析税单数据
   * 
   * @author han
   * @param xb
   * @param systemSheet
   * @param obj
   * @param evaluator
   * @return
   */
  public static List<SuiFuDan> SuiFuDanDeal(HSSFWorkbook xb, HSSFSheet systemSheet, JSONObject obj,
      FormulaEvaluator evaluator) {
    List<SuiFuDan> list = new ArrayList<SuiFuDan>();
    try {
      for (Map.Entry<String, Object> entry : obj.entrySet()) {
        systemSheet = xb.getSheet(entry.getKey());
        JSONObject val = (JSONObject) entry.getValue();

        for (int i = 0; i < fsdCount; i++) {
          SuiFuDan suiFuDan = new SuiFuDan();
          HashMap<String, Object> valMap = new HashMap<String, Object>();
          for (Map.Entry<String, Object> itemEntry : val.entrySet()) {
            String CellReferenceId = (String) itemEntry.getValue();
            if (i != 0) {
              int cellIndex =
                  Integer.parseInt(CellReferenceId.substring(1, CellReferenceId.length())) + i;
              CellReferenceId = CellReferenceId.substring(0, 1) + cellIndex;
            }
            CellReference cellReference = new CellReference(CellReferenceId);
            Row row = systemSheet.getRow(cellReference.getRow());
            Cell cell = row.getCell(cellReference.getCol());
            logger.info(itemEntry.getKey() + ":"
                + getRowItemVal(cellReference, row, cell, evaluator, itemEntry.getKey()));
            valMap.put(itemEntry.getKey(),
                getRowItemVal(cellReference, row, cell, evaluator, itemEntry.getKey()));
          }
          BeanUtils.copyProperties(suiFuDan, valMap);

          if (StringUtils.isEmpty(suiFuDan.getSuifudan_co())) {
            break;
          }
          list.add(suiFuDan);
        }
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    }
    return list;
  }

  /**
   * 商品处理数据
   * 
   * @author han
   * @param xb
   * @param systemSheet
   * @param obj
   * @param evaluator
   */
  public static MsgExcel productDeal(HSSFWorkbook xb, HSSFSheet systemSheet, JSONObject obj,
      FormulaEvaluator evaluator) {
    List<Commodity> list = new ArrayList<Commodity>();
    MsgExcel mesExcel = new MsgExcel();
    for (Map.Entry<String, Object> entry : obj.entrySet()) {
      systemSheet = xb.getSheet(entry.getKey());
      JSONObject val = (JSONObject) entry.getValue();

      for (int i = 0; i < importCount; i++) {
        Commodity commodity = new Commodity();
        HashMap<String, Object> valMap = new HashMap<String, Object>();
        for (Map.Entry<String, Object> itemEntry : val.entrySet()) {
          String CellReferenceId = (String) itemEntry.getValue();
          if (i != 0) {
            int cellIndex =
                Integer.parseInt(CellReferenceId.substring(1, CellReferenceId.length())) + 26 * i;
            CellReferenceId = CellReferenceId.substring(0, 1) + cellIndex;
          }
          try {
            CellReference cellReference = new CellReference(CellReferenceId);
            Row row = systemSheet.getRow(cellReference.getRow());
            Cell cell = row.getCell(cellReference.getCol());
            // if (itemEntry.getKey().equals("unit_price")) {
            // System.out.println("");
            // }
            // logger.info(itemEntry.getKey() + ":"
            // + getRowItemVal(cellReference, row, cell, evaluator, itemEntry.getKey()));
            String excelVal = "";
            try {
              excelVal = getRowItemVal(cellReference, row, cell, evaluator,
                  "第" + (i + 1) + "条商品数据的" + itemEntry.getKey());
            } catch (ImportException ex) {
              mesExcel.add(ex.getMessage());
            }
            String keyStr = itemEntry.getKey();
            if (keyStr.indexOf("|") > 0) {
              String[] keyAry = keyStr.split("\\|");
              valMap.put(keyAry[1], excelVal);
            } else {
              valMap.put(keyStr, excelVal);
            }
          } catch (Exception ex) {
            break;
          }
        }
        try {
          BeanUtils.copyProperties(commodity, valMap);
        } catch (Exception ex) {
          mesExcel.add("商品数据转换为Commodity出现异常");
        }
        commodity.setIndex_num((i + 1) + "");
        if (StringUtils.isEmpty(commodity.getName())) {
          break;
        }
        list.add(commodity);
      }
    }

    mesExcel.put("commoditys", list);
    return mesExcel;
  }

  /**
   * 处理各种表单类型
   * 
   * @author han
   * @param cellReference
   * @param row
   * @param cell
   * @param evaluator
   * @return
   */
  public static String getRowItemVal(CellReference cellReference, Row row, Cell cell,
      FormulaEvaluator evaluator, String key) throws ImportException {
    String rtnValue = "";
    if (cell.getCellType() == 1) {// 字符串
      CellValue cellValue = evaluator.evaluate(cell);
      rtnValue = cellValue.getStringValue();
    } else if (cell.getCellType() == 2) {// 公式型
      try {
        CellValue cellValue = evaluator.evaluate(cell);
        if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
          rtnValue = cellValue.getStringValue();
        } else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
          if (HSSFDateUtil.isCellDateFormatted(cell)) {
            double d = cell.getNumericCellValue();
            Date date = HSSFDateUtil.getJavaDate(d);
            SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd");
            rtnValue = time.format(date);
          } else if (cellValue.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            rtnValue = cell.getStringCellValue();
          } else {
            if (cell.getNumericCellValue() == 0) {
              rtnValue = null;
            } else {
              String numVal = cellValue.formatAsString().replaceAll("\"", "");
              rtnValue = writeInt(numVal);
            }
          }
        } else {
          rtnValue = cellValue.formatAsString();
        }
      } catch (Exception ex) {
        String afkey = "";
        if (key.indexOf("|") > 0) {
          afkey = key.split("\\|")[0];
        } else {
          afkey = key;
        }
        throw new ImportException(afkey + "");
      }
    } else if (cell.getCellType() == 3) {// 空值
      rtnValue = "";
    } else if (cell.getCellType() == 0) {// 数值型
      if (cell.getNumericCellValue() == 0) {
        rtnValue = null;
      } else {
        rtnValue = writeInt(cell.getNumericCellValue() + "");
      }
    }
    return rtnValue;
  }

  /**
   * 数值类型处理
   * 
   * @author han
   * @param strExcelLine
   * @return
   * @throws Exception
   */
  public static String writeInt(String strExcelLine) {
    String rtnVal = "";
    try {
      if (null != strExcelLine && strExcelLine.indexOf(".") != -1
          && strExcelLine.indexOf("E") != -1) {
        DecimalFormat df = new DecimalFormat();
        rtnVal = df.parse(strExcelLine).toString();
        return rtnVal;
      }

      // 如果读取的是数字格式，并且以".0"结尾格式，则转换为普通格式
      if (null != strExcelLine && strExcelLine.endsWith(".0")) {
        int size = strExcelLine.length();
        rtnVal = strExcelLine.substring(0, size - 2);
        return rtnVal;
      }

      DecimalFormat df = new DecimalFormat("#.####"); // 格式化为四位小数，按自己需求选择；
      Double doubleVal = Double.parseDouble(strExcelLine);
      rtnVal = String.valueOf(df.format(doubleVal)); // 返回String类型
    } catch (Exception ex) {
      return rtnVal;
    }

    return rtnVal;
  }

  /**
   * 定位属性拷贝引起的字段
   * 
   * @author han
   * @param str
   * @return
   */
  public static String getBeanCopyErrorFileName(String str, String regEx) {
    String fileName = null;
    // 创建 Pattern 对象
    Pattern r = Pattern.compile(regEx);
    // 现在创建 matcher 对象
    Matcher m = r.matcher(str);
    if (m.find()) {
      if (m.groupCount() >= 1)
        fileName = m.group(1);
    }
    return fileName;
  }
}
